package org.java.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

/**
 * 测试jdbc调用存储过程demo
 * Created by Dwyane-Wevil on 2016/12/24.
 */
public class ProcedureTest {

    private static String DB_DRIVER = "com.mysql.jdbc.Driver";
    private static String DB_CONNECTION = "jdbc:mysql://127.0.0.1:3306/shiro?useSSL=true";
    private static String DB_NAME = "root";
    private static String DB_PWd = "mysql";

    public static void main(String[] args) throws Exception {
        // 测试调用存储过程：无返回值
        //testProcNoOut();
        // 试调用存储过程：返回值是简单值非列表
        //testProcOutSimple();
        // 测试调用存过： db2 无返回值
        //testProcNoOutByDb2();
        // 测试调用存过： db2有返回值
        testProcOutByDb2();
    }

    /**
     * 测试调用存储过程：无返回值<br>
     * 存储过程代码：
     * <pre>
     *   CREATE PROCEDURE mysql.demo_in(in p_in int)
     *   BEGIN
     *      SELECT * FROM db;
     *   END;
     * </pre>
     * @throws Exception
     */
    public static void testProcNoOut() throws Exception {
        System.out.println("-------  start 测试调用存储过程：无返回值");
        Connection conn = null;
        CallableStatement callStmt = null;
        try {
            Class.forName(DB_DRIVER);
            conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
            // 存储过程 TEST_MICHAEL_NOOUT 其实是向数据库插入一条数据
            callStmt = conn.prepareCall("{call mysql.demo_in(?)}");

            // 参数
            callStmt.setInt(1, 1);
            callStmt.execute();
            System.out.println("-------  Test End.");
        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (null != callStmt) {
                callStmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        }
    }

    /**
     * 测试调用存储过程：无返回值, db2数据库<br>
     * 存储过程代码：
     * <pre>
     *   CREATE PROCEDURE mysql.demo_in(in p_in int)
     *   BEGIN
     *      SELECT * FROM db;
     *   END;
     * </pre>
     * @throws Exception
     */
    public static void testProcNoOutByDb2() throws Exception {
        DB_DRIVER = "com.ibm.db2.jcc.DB2Driver";
        DB_CONNECTION = "jdbc:db2://localhost:50000/CMMP:progressiveStreaming=2;fullyMaterializeLobData=false;traceLevel=3;currentSchema=XJDM;";
        DB_NAME = "CKC";
        DB_PWd = "CKC";
        System.out.println("-------  start 测试调用存储过程：无返回值");
        Connection conn = null;
        CallableStatement callStmt = null;
        try {
            Class.forName(DB_DRIVER);
            conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
            // 存储过程 TEST_MICHAEL_NOOUT 其实是向数据库插入一条数据
            callStmt = conn.prepareCall("{call XJDM.MODEL_1201(?)}");

            // 参数
            callStmt.setInt(1, 1);
            callStmt.execute();
            System.out.println("-------  Test End.");
        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (null != callStmt) {
                callStmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        }
    }

    /**
     * 测试调用存储过程：有返回值, db2数据库<br>
     * 存储过程代码：
     * <pre>
     *   CREATE PROCEDURE mysql.demo_in(in p_in int)
     *   BEGIN
     *      SELECT * FROM db;
     *   END;
     * </pre>
     * @throws Exception
     */
    public static void testProcOutByDb2() throws Exception {
        DB_DRIVER = "com.ibm.db2.jcc.DB2Driver";
        DB_CONNECTION = "jdbc:db2://localhost:50000/CMMP:progressiveStreaming=2;fullyMaterializeLobData=false;traceLevel=3;currentSchema=XJDM;";
        DB_NAME = "CKC";
        DB_PWd = "CKC";
        System.out.println("-------  start 测试调用存储过程：有返回值");
        Connection conn = null;
        CallableStatement callStmt = null;
        try {
            Class.forName(DB_DRIVER);
            conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
            // 存储过程 TEST_MICHAEL_NOOUT 其实是向数据库插入一条数据,有几个参数设置几个
            callStmt = conn.prepareCall("{call XJDM.SP_MODEL_201701(?,?,?)}");

            // 参数
            callStmt.setObject(3, "201701");

            // out 注册的index 和取值时要对应
            callStmt.registerOutParameter(1, Types.INTEGER);
            callStmt.registerOutParameter(2, Types.VARCHAR);

            // 执行
            callStmt.execute();
            System.out.println("-------  Test End.");
        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (null != callStmt) {
                callStmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        }
    }

    /**
     * 测试调用存储过程：返回值是简单值非列表<br>
     * 存过代码：
     * <pre>
     *  CREATE PROCEDURE mysql.demo_inout(in p_in int, out p_out int)
     *  BEGIN
     *      set p_out = 1;
     *      set p_in = 2;
     *      SELECT * FROM db;
     *  END;
     * </pre>
     * @throws Exception
     */
    public static void testProcOutSimple() throws Exception {
        System.out.println("-------  start 测试调用存储过程：返回值是简单值非列表");
        Connection conn = null;
        CallableStatement stmt = null;
        try {
            Class.forName(DB_DRIVER);
            conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);

            stmt = conn.prepareCall("{call mysql.demo_inout(?,?)}");

            stmt.setInt(1, 1);
            stmt.setInt(2, 2);

            // out 注册的index 和取值时要对应
            stmt.registerOutParameter(2, Types.INTEGER);
            stmt.execute();

            // getXxx(index)中的index 需要和上面registerOutParameter的index对应
            int i = stmt.getInt(2);
            System.out.println("符号条件的查询结果 count := " + i);
            System.out.println("-------  Test End.");
        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (null != stmt) {
                stmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        }
    }
}
